﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using Ccit.SysPlat.Log;
using Ccit.ClassLibrary;
using Newtonsoft.Json;

namespace Ccit.Utils
{
    /// <summary>
    /// 数据操作类
    /// </summary>
    public class DataInterface
    {
        #region init error log interface
        private static ILog _logInterface = Factory.CreateObject(MsErrLog.assemblyName, MsErrLog.className, 
                                                    ImportComponentType.DotNetDll, null) as ILog;
        private const string _funcPageName = "通用数据接口";

        private static string _dbType = HttpContext.Current.Application["DBType"].ToString();    
        #endregion
                   

        #region public static interface

        /// <summary>
        /// 获取数据表最大ID
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sPrimaryKey">主键名</param>
        /// <returns>0(异常情况) / 表中最大记录+1</returns>
        public static int GetMaxId(string sTableName, string sPrimaryKey)
        {
            return GetMaxId(sTableName, sPrimaryKey, string.Empty);
        }

        /// <summary>
        /// 获取数据表最大ID
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sPrimaryKey">主键名</param>
        /// <param name="sWhereClause">过滤条件</param>
        /// <returns>0(异常情况) / 表中最大记录+1</returns>
        public static int GetMaxId(string sTableName, string sPrimaryKey, string sWhereClause)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(sPrimaryKey)) return 0;

            string cmdText = string.Empty;
            if (_dbType == "4") //ACCESS
                cmdText = "SELECT (IIF(ISNULL(MAX(" + sPrimaryKey + ")),0,MAX(" + sPrimaryKey + ")) + 1) AS MAXID FROM " + sTableName + " WHERE 1=1 ";
            else
                cmdText = "SELECT (ISNULL(MAX(" + sPrimaryKey + "),0) + 1) AS MAXID FROM " + sTableName + " WHERE 1=1 ";

            cmdText += sWhereClause;

            string newMaxId = "0";
            int iNewRecId = 0;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(cmdText))
                {
                    if (iReader.Read())
                    {
                        newMaxId = iReader["MAXID"].ToString();
                    }
                }
                iNewRecId = Int32.Parse(newMaxId);
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = newMaxId
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + cmdText + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            return iNewRecId;
        }

        /// <summary>
        /// 获取数据表最大ID + 1(对于多主键情况)
        /// </summary>
        /// <param name="sTableName">数据表表名</param>
        /// <param name="valueKey">要获取最大值的字段名称</param>
        /// <param name="keysList">多主键集合(key[字段名称],value[字段值])</param>
        /// <returns></returns>
        public static int GetMaxId(string sTableName, string valueKey, SortedList<string, string> keysList)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(valueKey)
                        || (null == keysList) || (keysList.Count == 0)) return 0;

            StringBuilder sbCmdText = new StringBuilder();
            if(_dbType == "4")
                sbCmdText.Append("SELECT (IIF(ISNULL(MAX(").Append(valueKey).Append(")),0,MAX(").Append(valueKey).Append(")) + 1) AS MAXID FROM ").Append(sTableName).Append(" WHERE 1=1 ");
            else
                sbCmdText.Append("SELECT (ISNULL(MAX(").Append(valueKey).Append("),0) + 1) AS MAXID FROM ").Append(sTableName).Append(" WHERE 1=1 ");
                        
            int icount = keysList.Count;
            string skey = string.Empty;
            string keyValue = string.Empty;
            for (int i = 0; i < icount; i++)
            {
                skey = keysList.Keys[i];
                keyValue = keysList[skey];
                if(_dbType.Equals("4"))
                    sbCmdText.Append(" AND ").Append(skey).Append("=").Append(keyValue);                
                else
                    sbCmdText.Append(" AND ").Append(skey).Append("='").Append(keyValue).Append("'");                
            }

            string newMaxId = string.Empty;
            int iNewRecId = 0;
            try {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        newMaxId = iReader["MAXID"].ToString();                        
                    }                   
                }

                iNewRecId = Int32.Parse(newMaxId);
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = "通用接口",
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = newMaxId
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return iNewRecId;
        }

        /// <summary>
        /// 重名校验
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sIdField">Id字段</param>
        /// <param name="sNameField">名称字段名</param>
        /// <param name="sName">待校验的名称</param>
        /// <param name="sId">记录ID</param>
        /// <returns>true(存在) / false(不存在)</returns>
        public static bool CheckNameIsExist(string sTableName, string sIdField, string sNameField, string sName, string sId)
        {
            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT ").Append(sIdField).Append(" AS RECID FROM ").Append(sTableName);
            sbCmdText.Append(" WHERE ").Append(sNameField).Append("='").Append(sName.Replace("'", "''")).Append("'");

            bool bExist = false;
            string tempRecId = string.Empty;
            try {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                        tempRecId = iReader["RECID"].ToString();
                }

                bExist = (string.IsNullOrEmpty(tempRecId) || (tempRecId == sId)) ? false : true;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return bExist;
        }


        /// <summary>
        /// 获取新的一级层级代码
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sLayCodeField">层级代码字段名</param>
        /// <param name="iCodeLen">每级层级代码长度(如: 4位表示一级则传入4)</param>
        /// <param name="sWhereClause">过滤条件</param>
        /// <returns>新的层级代码</returns>
        public static string GetStairLayCode(string sTableName, string sLayCodeField, int iCodeLen,string sWhereClause)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(sLayCodeField))
            {
                string defValue = "0";
                defValue = defValue.PadLeft(iCodeLen, '0');
                return defValue;
            }

            StringBuilder sbCmdText = new StringBuilder();
            string orderClause = " ORDER BY " + sLayCodeField + " DESC";
            if (_dbType == "2") {//ORACLE
            
                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE LENGTH(").Append(sLayCodeField).Append(")=").Append(iCodeLen);                
            }
            else {// SQL SERVER、ACCESS
            
                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE LEN(").Append(sLayCodeField).Append(")=").Append(iCodeLen);                 
            }

            sbCmdText.Append(sWhereClause).Append(orderClause);

            string layCode = "0";
            long lLayCode = 0;
            try {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read())
                    {
                        layCode = iReader["LAYCODE"].ToString();
                    }
                }

                lLayCode = long.Parse(layCode);
                layCode = (lLayCode + 1).ToString().PadLeft(iCodeLen, '0');
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = layCode
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return layCode;           
        }

        /// <summary>
        /// 获取新的下级层级代码
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sLayCodeField">层级代码字段名</param>
        /// <param name="iCodeLen">每级层级代码长度(如: 4位表示一级则传入4)</param>
        /// <param name="upRecId">上级记录Id</param>
        /// <param name="sPrimaryKey">记录主键字段名</param>
        /// <param name="sWhereClause">过滤条件</param>
        /// <returns>新的下级层级代码</returns>
        public static string GetNewSubLayCode(string sTableName, string sLayCodeField, int iCodeLen,
                                                    string upRecId, string sPrimaryKey, string sWhereClause)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(sLayCodeField)
                            || string.IsNullOrEmpty(upRecId) || string.IsNullOrEmpty(sPrimaryKey)) return "0";
            
            StringBuilder sbCmdText = new StringBuilder();
            if (_dbType == "4") //ACCESS
            {
                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE ").Append(sPrimaryKey).Append("=").Append(upRecId);
            }
            else //SQL SERVER、ORACLE
            {
                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE ").Append(sPrimaryKey).Append("='").Append(upRecId).Append("'");
            }

            string upLayCode = string.Empty;  //上级层级代码
            int iUpCodeLen = 0;  //上级层级代码长度
            try
            {                
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read()) {
                        upLayCode = iReader["LAYCODE"].ToString();
                    }
                }

                iUpCodeLen = upLayCode.Length;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
                return "0";
            }

            sbCmdText.Remove(0, sbCmdText.ToString().Length);
            int subCodeLen = iUpCodeLen + iCodeLen; //子级别层级代码长度
 
            string orderClause = " ORDER BY " + sLayCodeField + " DESC";
            if (_dbType == "2")
            {//ORACLE

                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE LENGTH(").Append(sLayCodeField).Append(")=").Append(subCodeLen);
                sbCmdText.Append(" AND ").Append(sLayCodeField).Append(" LIKE '").Append(upLayCode).Append("%'");
            }
            else
            {// SQL SERVER、ACCESS

                sbCmdText.Append("SELECT ").Append(sLayCodeField).Append(" AS LAYCODE FROM ").Append(sTableName);
                sbCmdText.Append(" WHERE LEN(").Append(sLayCodeField).Append(")=").Append(subCodeLen);
                sbCmdText.Append(" AND ").Append(sLayCodeField).Append(" LIKE '").Append(upLayCode).Append("%'");
            }

            sbCmdText.Append(sWhereClause).Append(orderClause);

            string layCode = "0";
            long lLayCode = 0;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read())
                    {
                        layCode = iReader["LAYCODE"].ToString();
                    }
                }

                lLayCode = (layCode != "0") ? long.Parse(layCode.Substring(iUpCodeLen)) : 0;
                layCode = upLayCode + (lLayCode + 1).ToString().PadLeft(iCodeLen, '0');
            }
            catch (FormatException ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "002", //数据类型转换异常
                    paramInfo = layCode
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return layCode;  
        }


        /// <summary>
        /// 根据主键值获取值字段的字段值
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sKeyField">主键字段名</param>
        /// <param name="sValField">值字段名</param>
        /// <param name="sKeyValue">主键值</param>
        /// <returns>值字段的字段值</returns>
        public static string GetValueByKey(string sTableName, string sKeyField, string sValField, string sKeyValue)
        {
            return GetValueByKey(sTableName, sKeyField, sValField, sKeyValue, string.Empty); 
        }

        /// <summary>
        /// 根据主键值获取值字段的字段值
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sKeyField">主键字段名</param>
        /// <param name="sValField">值字段名</param>
        /// <param name="sKeyValue">主键值</param>
        /// <param name="sWhereClause">过滤条件</param>
        /// <returns>值字段的字段值</returns>
        public static string GetValueByKey(string sTableName, string sKeyField, string sValField,
                                                                    string sKeyValue,string sWhereClause)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(sKeyField)
                    || string.IsNullOrEmpty(sValField) || string.IsNullOrEmpty(sKeyValue)) return string.Empty;

            StringBuilder sbCmdText = new StringBuilder();
                        
            sbCmdText.Append("SELECT ").Append(sValField).Append(" AS ACTVALUE FROM ").Append(sTableName);
            if(_dbType == "4")
                sbCmdText.Append(" WHERE ").Append(sKeyField).Append("=").Append(sKeyValue).Append(" ");
            else
                sbCmdText.Append(" WHERE ").Append(sKeyField).Append("='").Append(sKeyValue).Append("' ");
            sbCmdText.Append(sWhereClause);

            string actValue = string.Empty;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                        actValue = iReader["ACTVALUE"].ToString();
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return actValue;
        }


        /// <summary>
        /// 获取多个主键值对应的值字段的字段值
        /// </summary>
        /// <param name="sTableName">表名</param>
        /// <param name="sKeyField">主键字段名</param>
        /// <param name="sValField">值字段名</param>
        /// <param name="sKeyValues">主键值串</param>
        /// <param name="sWhereClause">过滤条件</param>
        /// <param name="separator">用于分隔返回值之间的分隔符</param>
        /// <param name="enKeyType">主键类型枚举</param>
        /// <returns>值字段的字段值串</returns>
        public static string GetValuesByKeys(string sTableName, string sKeyField, string sValField, string sKeyValues,
                                                                string sWhereClause,string separator,ePrimaryKeyType enKeyType)
        {
            if (string.IsNullOrEmpty(sTableName) || string.IsNullOrEmpty(sKeyField)
                    || string.IsNullOrEmpty(sValField) || string.IsNullOrEmpty(sKeyValues)) return string.Empty;

            separator = (string.IsNullOrEmpty(separator)) ? " " : separator; //默认空格分隔符

            if (enKeyType == ePrimaryKeyType.STRING)
            {
                string[] keyValArray = sKeyValues.Split(',');
                int itemCount = keyValArray.Length;                
                string keyValues = string.Empty;
                for (int i = 0; i < itemCount; i++)
                {
                    keyValues += (string.IsNullOrEmpty(keyValues)) ? "'" + keyValArray[i] + "'" : ",'" + keyValArray[i] + "'";
                }
                sKeyValues = keyValues;
            }

            StringBuilder sbCmdText = new StringBuilder();
            sbCmdText.Append("SELECT ").Append(sValField).Append(" AS ACTVALUE FROM ").Append(sTableName);
            sbCmdText.Append(" WHERE ").Append(sKeyField).Append(" IN (").Append(sKeyValues).Append(") ");
            sbCmdText.Append(sWhereClause);

            string actValue = string.Empty;
            string actValues = string.Empty;
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    if (iReader.Read() && null != iReader[0])
                    {
                        actValue = iReader["ACTVALUE"].ToString();
                        actValues += (string.IsNullOrEmpty(actValues)) ? actValue : separator + actValue;
                    }
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            sbCmdText = null;
            return actValues;
        }

        /// <summary>
        /// 获取当前节点的下一级子节点集合
        /// 此接口只适用于以层级编码的形式组织的结构
        /// </summary>
        /// <typeparam name="T">返回子节点类型(泛型)</typeparam>
        /// <param name="sTableName">表名</param>
        /// <param name="fieldAttrDict">要获取的字段-消息对象属性字段对儿集合(如:NEWS_A2_20 - colName)</param>
        /// <param name="MsPrimaryKey">主键-主键值 键值对象 (NEWS_A2_10 - 3)</param>
        /// <param name="layFieldName">层级编码字段(如:NEWS_A2_30)</param>
        /// <param name="iCodeLen">每级的层级编码长度</param>
        /// <param name="hasChildAttrName">消息对象中的是否有下级子节点的属性名</param>
        /// <param name="whereClause">过滤条件
        ///   格式:
        ///       "AND A.NEWS_A2_140='0'"
        /// </param>
        /// <returns></returns>
        public static List<T> GetSubItemList<T>(string sTableName, Dictionary<string, string> fieldAttrDict, MsKeyValue MsPrimaryKey,
                                string layFieldName, int iCodeLen,string hasChildAttrName, string whereClause)
        {
            string primaryKey = MsPrimaryKey.key;
            string keyValue = MsPrimaryKey.val;

            StringBuilder sbCmdText = new StringBuilder("SELECT ");
            int fieldIndex = 0;
            foreach(KeyValuePair<string,string> eField in fieldAttrDict)
            {
                if(fieldIndex == 0)
                    sbCmdText.Append("A.").Append(eField.Key).Append(" AS ").Append(eField.Value);
                else
                    sbCmdText.Append(",A.").Append(eField.Key).Append(" AS ").Append(eField.Value);
                fieldIndex++;
            }

            if (_dbType == "4") //ACCESS
                sbCmdText.Append(",IIF(ISNULL(C.SUBCOUNT),0,C.SUBCOUNT) AS SUBCOUNT ");
            else //SQL SERVER、ORACLE
                sbCmdText.Append(",ISNULL(C.SUBCOUNT,0) AS SUBCOUNT ");            

            if (string.IsNullOrEmpty(keyValue)) //获取所有一级节点
            {
                if (_dbType == "4")
                {
                    sbCmdText.Append("FROM (SELECT * FROM ").Append(sTableName).Append(" WHERE LEN(").Append(layFieldName).Append(")=").Append(iCodeLen.ToString()).Append(") A ");
                    sbCmdText.Append("LEFT JOIN (SELECT MID(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(") AS LAYCODE,COUNT(*) AS SUBCOUNT FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("WHERE 1=1 AND LEN(A.").Append(layFieldName).Append(")=").Append((iCodeLen * 2).ToString()).Append(" ").Append(whereClause).Append(" GROUP BY  MID(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(")) C ON A.").Append(layFieldName).Append("=C.LAYCODE ");
                    sbCmdText.Append("WHERE 1=1 ").Append(whereClause);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }
                else if (_dbType == "1") { //SQL SERVER
                    sbCmdText.Append("FROM (SELECT * FROM ").Append(sTableName).Append(" WHERE LEN(").Append(layFieldName).Append(")=").Append(iCodeLen.ToString()).Append(") A ");
                    sbCmdText.Append("LEFT JOIN (SELECT SUBSTRING(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(") AS LAYCODE,COUNT(*) AS SUBCOUNT FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("WHERE 1=1 AND LEN(A.").Append(layFieldName).Append(")=").Append((iCodeLen * 2).ToString()).Append(" ").Append(whereClause).Append(" GROUP BY  SUBSTRING(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(")) C ON A.").Append(layFieldName).Append("=C.LAYCODE ");
                    sbCmdText.Append("WHERE 1=1 ").Append(whereClause);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }
                else { //ORACLE
                    sbCmdText.Append("FROM (SELECT * FROM ").Append(sTableName).Append(" WHERE LENGTH(").Append(layFieldName).Append(")=").Append(iCodeLen.ToString()).Append(") A ");
                    sbCmdText.Append("LEFT JOIN (SELECT SUBSTRING(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(") AS LAYCODE,COUNT(*) AS SUBCOUNT FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("WHERE 1=1 AND LENGTH(A.").Append(layFieldName).Append(")=").Append((iCodeLen * 2).ToString()).Append(" ").Append(whereClause).Append(" GROUP BY  SUBSTRING(A.").Append(layFieldName).Append(",1,").Append(iCodeLen.ToString()).Append(")) C ON A.").Append(layFieldName).Append("=C.LAYCODE ");
                    sbCmdText.Append("WHERE 1=1 ").Append(whereClause);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }

                
                #region 参考SQL tim 2010-02-04
                //SELECT A.NEWS_A2_10 AS COLID,A.NEWS_A2_20 AS COLNAME ,A.NEWS_A2_30 AS LAYCODE,ISNULL(C.COLCOUNT,0) AS COLCOUNT
                //FROM (SELECT * FROM NEWS_A2 WHERE LEN(NEWS_A2_30)=4) A
                //LEFT JOIN (SELECT SUBSTRING(A.NEWS_A2_30,1,4) AS LAYCODE,COUNT(*) AS COLCOUNT FROM NEWS_A2 A
                //WHERE A.NEWS_A2_140='0' AND LEN(A.NEWS_A2_30)=8 GROUP BY  SUBSTRING(A.NEWS_A2_30,1,4)) C ON A.NEWS_A2_30=C.LAYCODE 
                //WHERE A.NEWS_A2_140='0'
                #endregion
            }
            else  //获取传入主键的所有下一级子节点
            {
                if(_dbType == "4") //ACCESS
                {
                    sbCmdText.Append("FROM (").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("=").Append(keyValue).Append(") D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%') ");
                    sbCmdText.Append("INNER JOIN (SELECT MID(A.").Append(layFieldName).Append(",1,(LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(")) AS LAYCODE,COUNT(*) AS SUBCOUNT ");
                    sbCmdText.Append("FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("=").Append(keyValue).Append(") D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%' ");
                    sbCmdText.Append("WHERE 1=1 AND (LEN(A.").Append(layFieldName).Append(") >= (LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(") AND LEN(A.").Append(layFieldName).Append(") <= (LEN(D.LAYCODE) + ").Append((iCodeLen * 2).ToString()).Append(")) ").Append(whereClause);
                    sbCmdText.Append("GROUP BY MID(A.").Append(layFieldName).Append(",1,(LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append("))) C ON C.LAYCODE=A.").Append(layFieldName);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }
                else if (_dbType == "1") { //SQL SERVER
                    sbCmdText.Append("FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("='").Append(keyValue).Append("') D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%' ");
                    sbCmdText.Append("INNER JOIN (SELECT SUBSTRING(A.").Append(layFieldName).Append(",1,(LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(")) AS LAYCODE,COUNT(*) AS SUBCOUNT ");
                    sbCmdText.Append("FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("='").Append(keyValue).Append("') D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%' ");
                    sbCmdText.Append("WHERE 1=1 AND (LEN(A.").Append(layFieldName).Append(") >= (LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(") AND LEN(A.").Append(layFieldName).Append(") <= (LEN(D.LAYCODE) + ").Append((iCodeLen * 2).ToString()).Append(")) ").Append(whereClause);
                    sbCmdText.Append("GROUP BY SUBSTRING(A.").Append(layFieldName).Append(",1,(LEN(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append("))) C ON C.LAYCODE=A.").Append(layFieldName);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }
                else { //ORACLE
                    sbCmdText.Append("FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("='").Append(keyValue).Append("') D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%' ");
                    sbCmdText.Append("INNER JOIN (SELECT SUBSTRING(A.").Append(layFieldName).Append(",1,(LENGTH(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(")) AS LAYCODE,COUNT(*) AS SUBCOUNT ");
                    sbCmdText.Append("FROM ").Append(sTableName).Append(" A ");
                    sbCmdText.Append("INNER JOIN (SELECT ").Append(layFieldName).Append(" AS LAYCODE FROM ").Append(sTableName).Append(" WHERE ").Append(primaryKey).Append("='").Append(keyValue).Append("') D ON A.").Append(layFieldName).Append(" LIKE '' + D.LAYCODE + '%' ");
                    sbCmdText.Append("WHERE 1=1 AND (LENGTH(A.").Append(layFieldName).Append(") >= (LENGTH(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append(") AND LENGTH(A.").Append(layFieldName).Append(") <= (LENGTH(D.LAYCODE) + ").Append((iCodeLen * 2).ToString()).Append(")) ").Append(whereClause);
                    sbCmdText.Append("GROUP BY SUBSTRING(A.").Append(layFieldName).Append(",1,(LENGTH(D.LAYCODE) + ").Append(iCodeLen.ToString()).Append("))) C ON C.LAYCODE=A.").Append(layFieldName);
                    sbCmdText.Append(" ORDER BY A.").Append(layFieldName).Append(" ASC");
                }

                
                #region 参考SQL tim 2010-02-03
                //SELECT A.NEWS_A2_10 AS COLID,A.NEWS_A2_20 AS COLNAME ,A.NEWS_A2_30 AS LAYCODE,C.COLCOUNT
                //FROM NEWS_A2 A
                //INNER JOIN (SELECT NEWS_A2_30 AS LAYCODE FROM NEWS_A2 WHERE NEWS_A2_10='31') D ON A.NEWS_A2_30 LIKE '' + D.LAYCODE + '%'
                //INNER JOIN (SELECT SUBSTRING(A.NEWS_A2_30,1,(LEN(D.LAYCODE) + 4)) AS LAYCODE,COUNT(*) AS COLCOUNT
                //FROM NEWS_A2 A
                //INNER JOIN (SELECT NEWS_A2_30 AS LAYCODE FROM NEWS_A2 WHERE NEWS_A2_10='31') D ON A.NEWS_A2_30 LIKE '' + D.LAYCODE + '%'
                //WHERE 1=1 AND (LEN(A.NEWS_A2_30) >= (LEN(D.LAYCODE) + 4) AND LEN(A.NEWS_A2_30) <= (LEN(D.LAYCODE) + 8)) AND A.NEWS_A2_140='0'
                //GROUP BY SUBSTRING(A.NEWS_A2_30,1,(LEN(D.LAYCODE) + 4)) ) C ON C.LAYCODE=A.NEWS_A2_30
                //ORDER BY A.NEWS_A2_130 ASC
                #endregion
            }
            StringBuilder sbJson = new StringBuilder("[");
            string altName = string.Empty; //别名
            int rowIndex = 0;
            int colIndex = 0;
            string hasChild = "0";  //是否有下级子节点 0:没有 1:有
            try
            {
                using (IDataReader iReader = DataAccessor.ExecuteReader(sbCmdText.ToString()))
                {
                    while (iReader.Read())
                    {
                        if (rowIndex > 0) sbJson.Append(",");

                        colIndex = 0;
                        foreach (KeyValuePair<string, string> eField in fieldAttrDict)
                        {
                            altName = eField.Value;
                            if (colIndex == 0)
                                sbJson.Append("{'").Append(altName).Append("':'").Append(iReader[altName].ToString()).Append("'");
                            else
                                sbJson.Append(",'").Append(altName).Append("':'").Append(iReader[altName].ToString()).Append("'");
                            colIndex++;
                        }
                        /* 业务说明:
                         *    由于取一级节点的SQL中的SUBCOUNT列只包含一级节点的下一级节点数，并不包含一级节点本身，因此大于0就代表包含下级子节点 
                         *    而取非一级节点时的SQL中的SUBCOUNT列则包含本级节点，因此要大于1则代表包含下级子节点 
                         * tim 2010-02-23 */
                        hasChild = ((!string.IsNullOrEmpty(keyValue) && (Int32.Parse(iReader["SUBCOUNT"].ToString()) > 1)) ||
                                    (string.IsNullOrEmpty(keyValue) && (Int32.Parse(iReader["SUBCOUNT"].ToString()) > 0))) ? "1" : "0";
                        sbJson.Append(",'").Append(hasChildAttrName).Append("':'").Append(hasChild).Append("'");

                        sbJson.Append("}");
                        rowIndex++;
                    }

                    sbJson.Append("]");
                }
            }
            catch (Exception ex)
            {
                MsErrLog msErrorLog = new MsErrLog
                {
                    funcPageName = _funcPageName,
                    errMsg = ex.Message,
                    codeTrack = ex.StackTrace,
                    errClassCode = "001", //SQL 异常
                    paramInfo = "<SQL><![CDATA[" + sbCmdText.ToString() + "]]></SQL>"
                };

                _logInterface.WriteErrorLog(msErrorLog);
                msErrorLog = null;
            }

            return (List<T>)JavaScriptConvert.DeserializeObject(sbJson.ToString(), typeof(List<T>));

        }
        #endregion
    }

    /// <summary>
    /// 数据表主键类型枚举
    /// </summary>
    public enum ePrimaryKeyType
    {
        /// <summary>
        /// 数字
        /// </summary>
        NUMBER = 1,

        /// <summary>
        /// 字符串
        /// </summary>
        STRING = 2
    }
}
